<?php

/**
 * Description of Report_withdrawal_ewalletController
 *
 * @author TEJO MURTI
 */

class CashflowController extends AdminController {

    public function actionView() {
        $session=Yii::app()->session;

        /*
         * tambahan by wawan, income factory rincianya diambil dari sys_serial_products_user
        */

        $income['umroh'] = Yii::app()->db->createCommand();
        $income['umroh']->select('count(`serial_products_user_id`) AS Jumlah');
        $income['umroh']->from('sys_serial_products_user');
        $income['umroh']->where("serial_products_user_serial_products_id like '%FACT%'");

        $income['haji'] = Yii::app()->db->createCommand();
        $income['haji']->select('count(`serial_products_user_id`) AS Jumlah');
        $income['haji']->from('sys_serial_products_user');
        $income['haji']->where("serial_products_user_serial_products_id like '%ELHJ%'");

        $income['produk'] = Yii::app()->db->createCommand();
        $income['produk']->select('count(`serial_products_user_id`) AS Jumlah');
        $income['produk']->from('sys_serial_products_user');
        $income['produk']->where("serial_products_user_serial_products_id like '%FPRO%'");

        $income['edukasi'] = Yii::app()->db->createCommand();
        $income['edukasi']->select('count(`serial_products_user_id`) AS Jumlah');
        $income['edukasi']->from('sys_serial_products_user');
        $income['edukasi']->where("serial_products_user_serial_products_id like '%EDUC%'");

        $income['umhj'] = Yii::app()->db->createCommand();
        $income['umhj']->select('count(`serial_products_user_id`) AS Jumlah');
        $income['umhj']->from('sys_serial_products_user');
        $income['umhj']->where("serial_products_user_serial_products_id like '%UMHJ%'");

        /*
         * Ewallet
         */
        
        $ewallet = Yii::app()->db->createCommand();
        $ewallet->select('sum(ewallet_income_cash) as cash,sum(ewallet_income_products) as product,sum(ewallet_income_voucher) as voucher,sum(ewallet_income_extra) as extra');
        $ewallet->from('sys_ewallet_income');
        
        /*
         * withdrawal
         */
        $withdrawal = Yii::app()->db->createCommand();
        $withdrawal->select('sum(ewallet_log_value) as total,ewallet_log_type');
        $withdrawal->from('sys_ewallet_log');
        $withdrawal->where("ewallet_log_status='debit'");
        $withdrawal->group('ewallet_log_type');
        
        $command_income=Yii::app()->db->createCommand();
        $command_income->select('`network_level_status_log_value`, count(`network_level_status_log_id`) AS Jumlah');
        $command_income->from('sys_network_level_status_log');
        $command_income->where(array('not in','network_level_status_log_value','passive'));
        $command_income->group('network_level_status_log_value');


        $data_table=array('sponsor','extra_sponsor','gen_node','match','matrix_node','gen_node_extra','board_fly','board_level');
        foreach($data_table AS $val) {

            $command[$val]=Yii::app()->db->createCommand();
            $command[$val]->select('SUM('.$val.'_bonus_log_value) AS total_'.$val);
            $command[$val]->from('sp_'.$val.'_bonus_log');

        }


        if(isset($_GET['month']) AND is_numeric($_GET['month'])) {
            $command_income->where('network_level_status_log_value!="passive"
					AND month(network_level_status_log_datetime)=:month',array(':month'=>$_GET['month']));
            $income['umroh']->where('MONTH(serial_products_user_datetime)=:month',array(':month'=>$_GET['month']));
            $income['haji']->where('MONTH(serial_products_user_datetime)=:month',array(':month'=>$_GET['month']));
            $income['produk']->where('MONTH(serial_products_user_datetime)=:month',array(':month'=>$_GET['month']));
            $income['edukasi']->where('MONTH(serial_products_user_datetime)=:month',array(':month'=>$_GET['month']));
            $income['umhj']->where('MONTH(serial_products_user_datetime)=:month',array(':month'=>$_GET['month']));
            
            $ewallet->where('MONTH(ewallet_income_date) = :month',array(':month'=>$_GET['month']));
            
            $withdrawal->where('MONTH(ewallet_log_datetime) = :month',array(':month'=>$_GET['month']));
            
            foreach($data_table AS $val) {
                $command[$val]->where('month('.$val.'_bonus_log_date)=:month',array(':month'=>$_GET['month']));
            }

        }
        if(isset($_GET['years']) AND is_numeric($_GET['years'])) {
            $command_income->where('network_level_status_log_value!="passive"
					AND year(network_level_status_log_datetime)="'.$_GET['year'].'"');
            $income['umroh']->where('year(serial_products_user_datetime)=:year',array(':year'=>$_GET['year']));
            $income['haji']->where('year(serial_products_user_datetime)=:year',array(':year'=>$_GET['year']));
            $income['produk']->where('year(serial_products_user_datetime)=:year',array(':year'=>$_GET['year']));
            $income['edukasi']->where('year(serial_products_user_datetime)=:year',array(':year'=>$_GET['year']));
            $income['umhj']->where('year(serial_products_user_datetime)=:year',array(':year'=>$_GET['year']));
            
            $ewallet->where('YEAR(ewallet_income_date) = :year',array(':year'=>$_GET['year']));
            $withdrawal->where('YEAR(ewallet_log_datetime) = :year',array(':year'=>$_GET['year']));
            
            foreach($data_table AS $val) {
                $command[$val]->where('year('.$val.'_bonus_log_date)=:year',array(':year'=>$_GET['years']));
            }
        }
        if(isset($_GET['month']) AND is_numeric($_GET['month']) AND isset($_GET['years']) AND is_numeric($_GET['years'])) {
            $command_income->where('network_level_status_log_value!="passive"
					AND month(network_level_status_log_datetime)=:month
					AND year(network_level_status_log_datetime)=:year',
                    array(':month'=>$_GET['month'],
                    ':year'=>$_GET['years']));

            $income['umroh']->where('MONTH(serial_products_user_datetime)=:month AND year(serial_products_user_datetime)=:year',array(':month'=>$_GET['month'],':year'=>$_GET['year']));
            $income['haji']->where('MONTH(serial_products_user_datetime)=:month AND year(serial_products_user_datetime)=:year',array(':month'=>$_GET['month'],':year'=>$_GET['year']));
            $income['produk']->where('MONTH(serial_products_user_datetime)=:month AND year(serial_products_user_datetime)=:year',array(':month'=>$_GET['month'],':year'=>$_GET['year']));
            $income['edukasi']->where('MONTH(serial_products_user_datetime)=:month AND year(serial_products_user_datetime)=:year',array(':month'=>$_GET['month'],':year'=>$_GET['year']));
            $income['umhj']->where('MONTH(serial_products_user_datetime)=:month AND year(serial_products_user_datetime)=:year',array(':month'=>$_GET['month'],':year'=>$_GET['year']));
            
            $ewallet->where('YEAR(ewallet_income_date) = :year AND MONTH(ewallet_income_date) = :month',array(':month'=>$_GET['month'],':year'=>$_GET['year']));
            $withdrawal->where('YEAR(ewallet_log_datetime) = :year AND MONTH(ewallet_log_datetime) = :month',array(':month'=>$_GET['month'],':year'=>$_GET['year']));
            
            foreach($data_table AS $val) {
                $command[$val]->where('month('.$val.'_bonus_log_date)=:month
						AND year('.$val.'_bonus_log_date)=:year',
                        array(':month'=>$_GET['month'],
                        ':year'=>$_GET['years']));
            }


        }
        $results_income=$command_income->queryAll();

        $result['umroh']=$income['umroh']->queryAll();
        $result['haji']=$income['haji']->queryAll();
        $result['produk']=$income['produk']->queryAll();
        $result['edukasi']=$income['edukasi']->queryAll();
        $result['umhj']=$income['umhj']->queryAll();
        
        $result_ewallet = $ewallet->queryAll();
        $result_withdrawal = $withdrawal->queryAll();
        
        $data_arr=array();
        foreach($data_table AS $val) {
            $results[$val]=$command[$val]->queryRow();
            $data_arr[$val]=$results[$val];
        }

        $dataProviderIncome=array(
                array(
                        'network_level_status_log_value'=>'Distributor',
                        'Jumlah'=>0,
                ),
                array(
                        'network_level_status_log_value'=>'Warehouse',
                        'Jumlah'=>0,
                ),
                array(
                        'network_level_status_log_value'=>'Factory',
                        'Jumlah'=>0,
                ),
        );


        $dataProviderValue=!empty($results_income)?$results_income:$dataProviderIncome;
        $dataProviderIncome=new CArrayDataProvider($dataProviderValue,array(
                        'keyField'=>'network_level_status_log_value',
                        'sort'=>array(
                                'attributes'=>array('Jumlah'),
                        ),
        ));


//echo '<pre>';print_r($results['sponsor']);
        $this->render('view',
                array(
                'income' => $result,
                'withdraw' => $result_withdrawal,    
                'ewallet' => $result_ewallet,    
                'result_income' => $results_income,
                'results'=>$data_arr,
                'dataProviderIncome'=>$dataProviderIncome,
        ));
    }


}

?>
